using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using BlogEngine.Core;

namespace BlogEngine.Core.Providers
{
	/// <summary>
	/// Microsoft SQL Server Implementation of BlogProvider
	/// </summary>
	public partial class MSSQLBlogProvider : BlogProviderBase
	{
		/// <summary>
		/// Gets a Category based on a Guid
		/// </summary>
		/// <param name="id">The category's Guid.</param>
		/// <returns>A matching Category</returns>
		public override Category SelectCategory(Guid id)
		{
			Category cat = new Category();
			SQLManagedConnection myConn = SQLManagedConnection.GetConnection(_DatabaseName);

			try
			{
				myConn.GetTextCommand("SELECT CategoryName, Description FROM be_Categories WHERE CategoryID = @id");
				myConn.AddInParameter("@id", DbType.Guid, id);

				using (IDataReader rdr = myConn.ExecuteReader())
				{
					rdr.Read();

					cat.Id = id;
					cat.Title = rdr.GetString(0);					
					if (!rdr.IsDBNull(1))
						cat.Description = rdr.GetString(1);

					cat.MarkOld();
					rdr.Close();
				}
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}

			return cat;
		}

		/// <summary>
		/// Inserts a Category
		/// </summary>
		/// <param name="category">Must be a valid Category object.</param>
		public override void InsertCategory(Category category)
		{
			if (category == null)
				throw new ArgumentNullException("category");

			SQLManagedConnection myConn = new SQLManagedConnection(_DatabaseName);

			try
			{
				myConn.GetTextCommand("INSERT INTO be_Categories (CategoryID, CategoryName, Slug, Description) VALUES (@catid, @catname, @catslug, @catdesc)");
				myConn.AddInParameter("@catid", DbType.Guid, category.Id);
				myConn.AddInParameter("@catname", DbType.String, category.Title);
				myConn.AddInParameter("@catslug", DbType.String, category.Slug);
				myConn.AddInParameter("@catdesc", DbType.String, category.Description);
				myConn.ExecuteNonQuery();

				Category.Items.Add(category);
				Category.Items.Sort();
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}
		}

		/// <summary>
		/// Updates a Category
		/// </summary>
		/// <param name="category">Must be a valid Category object.</param>
		public override void UpdateCategory(Category category)
		{
			if (category == null)
				throw new ArgumentNullException("category");

			List<Category> categories = Category.Items;

			SQLManagedConnection myConn = new SQLManagedConnection(_DatabaseName);

			try
			{
				myConn.GetTextCommand("UPDATE be_Categories " +
									  "SET CategoryName = @catname," +
									  "Slug         = @catslug," +
									  "Description  = @catdesc " +
									  "WHERE CategoryID = @catid");

				myConn.AddInParameter("@catid", DbType.Guid, category.Id);
				myConn.AddInParameter("@catname", DbType.String, category.Title);
				myConn.AddInParameter("@catslug", DbType.String, category.Slug);
				myConn.AddInParameter("@catdesc", DbType.String, category.Description);
				myConn.ExecuteNonQuery();

				if (categories.Contains(category))
					categories.Remove(category);

				categories.Add(category);
				categories.Sort();
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}
		}

		/// <summary>
		/// Deletes a Category
		/// </summary>
		/// <param name="category">Must be a valid Category object.</param>
		public override void DeleteCategory(Category category)
		{
			if (category == null)
				throw new ArgumentNullException("category");

			SQLManagedConnection myConn = new SQLManagedConnection(_DatabaseName);

			try
			{
				myConn.GetTextCommand("DELETE FROM be_PostCategory WHERE CategoryID = @catid;" +
					  "DELETE FROM be_Categories WHERE CategoryID = @catid");
				myConn.AddInParameter("@catid", DbType.Guid, category.Id);
				myConn.ExecuteNonQuery();

				if (Category.Items.Contains(category))
					Category.Items.Remove(category);

				Category.Items.Sort();
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}
		}

		/// <summary>
		/// Fills a sorted list of categories.
		/// </summary>
		/// <returns>A List&lt;Category&gt; of all Categories.</returns>
		public override List<Category> FillCategories()
		{
			List<Category> categories = new List<Category>();

			//for each entry found, cause a data load to occur - done this way so the reader is shut first!
			foreach (Guid guid in this.GetGuids("SELECT CategoryID FROM be_Categories"))
				categories.Add(Category.Load(guid));

			categories.Sort();
			return categories;
		}
	}
}
